##parameters=form

fulltext  = form.get("fulltext",None)
daterange = dict( form.get("date", {}) )
username  = form.get("username",None)
modelIdentity = form.get("modelIdentity", None)
onlyComments = form.get("onlyComments", None)

user = context.REQUEST.AUTHENTICATED_USER

# Do we want to limit to Managers only?
if not user.has_role('Manager'):
  raise "Access Denied"

db = context.knowledgeengine_assessmentstorageservice

sql = """
    SELECT workflow.*, assessments.name as assessmentName, assessments.modelIdentity
    FROM workflow 
    INNER JOIN assessments ON (assessments.assessmentIdentity = workflow.assessmentIdentity)
    
    WHERE 1 
    """

if onlyComments:
    sql += " AND ( comments != '' AND comments is not null )"

if fulltext:
    sql += " AND ( comments LIKE '%%%s%%' )" % db.qstring( fulltext, quote=False )
    
if username:
    sql += " AND workflow.userIdentity = %s" % db.qstring( username )
    
if modelIdentity:
    sql += " AND assessments.modelIdentity = %s" % db.qstring( modelIdentity )  
    
if daterange:
    start = daterange.get("start", None)
    end   = daterange.get("end", None)
        
    if start:
        sql += " AND `when` >= %s " % db.qstring( start )
    
    if end:
        sql += " AND `when` <= %s " % db.qstring( end )

#raise sql       
        
results = db.sqlQuery( sql )

return results

